SQL Server --> Oracle (һ) 
Support Oracle
zhuzhichao 2002.4

׌Oracle_lĳʽSupport SQL ServerSp;׌SQL Server_lSupport OracleɲN,挦Ć}KҪஔĹ.
OracleõķԺͰȫܵ˱DBAƳͺu,ĿɲԺҲ׌^Programmerеʹ^.
SQL ServerT-SQLN`,OraclePL/SQLNć̰֔,SQL Server --> Oracle׌еNĲT.
Ƿ˃δ{ͨһ300еsp?
Ƿһy}`˺Î칤ځKݚDy?

,Ո^mă.

һ. basic
. ηؽY?
. PRr
. ЄӑBSQL
. identityֲ?
. P|l
. Y,Y,ȫY
. ҪF2_߀Щ?
. Top ncrownum
ʮ. ܃
ʮһ. 

......

. ηؽY?
.OraclespܷؽY!!!
SQL ServerOracleĹǷl@ӵ@?
OracleκPL/SQLZKеselectZҪintoӾ!
@OraclespܷؽYԭ!
κε鶼Qk,ǧfҪ˶H.ǽQ:
create or replace package pkg_test 
as
type cur_test is ref cursor; -- xһcursortype
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); -- 
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then 
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm ); 
end p_test;
/
Javaʽ:

CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// ]outSQL
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// ȡõÔݽY
while(rs.next())



......
. PRr
һڳʽкSQL ServerspаlF˺ܶõRrĵط;
ҲһlFOraclespвcreate table;
߀һlFԭOracleеRrSQL ServerеRr
һ,o_SQL ServerRr!
@һжǞʲN???֌νQ?

@NɆ,҂ȁһSQL ServerRr.
SQL ServerRrЃɷN:ֲRr(#temp)ȫRr(##temp)
ֲRrǪÿһsession,ÿһsessionf,㶼createһͬ#tempÿsessionԼ#temp,಻ɔ_.session˳ԄጷԼ#temp.
ȫRrһ,еsessionʹ,ֻ^ֻЮȫRrsession˳,ȫRrԄጷ.
ֵһ,SQL ServerRrȫ惦tempdb,SQL SERVER6.5tempdb in RAMx,7.0֮İ汾mȻȡԓ.ֻҪtempdbӲPg,ڶÑlrRrܾͲ.KÿһLSQL SERVERԄӕtempdbMƬԴ_.
OracleеRrSQL ServerеRr̎CȫȻͬ.OracleRr픶ֻஔSQL ServerеȫRr,ô,ԼȥdropԒ,OracleǲԄӌጷŵ.SQL ServerеľֲRrOracleПo,OracleзŗˌRrʹ,еRrȫĞñ.
OraclespвܳFDDLZ.еDDLZ(create,drop,alter,truncate)@ʽcommit,Oraclespв@ʽcommitĴ.Ҫspн߄hԒ,ÄӑBSQL[ʽcommit.
:
execute immediate create table ;
P춄ӑBSQL,挢һԔĽB.

@eпЃɂ`^,̎Ӱܻߌ³ʽe:
1. spһ_ʼֱÄӑBSQLñ,ȻጦMв,sp˳֮ǰÄӑBSQLhñ.
Ոע,ڶÑlĕr.A session{ԓspKɹ˱, B sessionҲ{ԓspԇDȥ@ĕr.OracleҰUČspД,Ȼ҂Ŀ͕͑ܲѺõĳe.
2. ׌Ñʹûɔ_,ɳʽsessionidspOracleĺuserenv(sessionid)sessionid.ȻspÄӑBSQLɱ+sessionidı,ԓMв,sp˳rh.
@ӕһ}:sp{Ì²Ľh.Oracleıڱg.@ӴDDLZ׌gȵƬg,Ҫ֪OracleƬҪքM,SQL SERVERǘԄ.Oracle׌˝Mԃ,Oracle׌˲M횃!^һһ,҂Ñ͕҂Թ҂ϵyܵԽԽ.ᵽǰf^Ԓ:҂Ľ^ʽ]ܵĿ,]Ԓ,҂ϵyЇصĆ}.

ԭSQL SERVERеRrֲOracleҵ̎:
1. 1-Module-CreateTempTable.sqlļ:
ԭSQL SERVERõRrڴ˽,ֻÿһֶsessionid int,I,NsessionidI.
2. 0-Module-DropTempTable.sqlļ,eă:
begin
for r in (select 'drop table '||object_name as sqls from user_objects where object_type = ' TABLE' and object_name in (temp1,temp2,)) loop -- еRrin
execute immediate (r.sqls);
end loop;
end;
@ɂSQLļ׌҂ĳʽԄ?
3. ɳʽsessionidͨ^userenv(sessionid) sessionidsessionidֶ.ÿsessionֻ̎sessionĔ.

ע:Oracle·ʽRrͬSQL ServerȫRrЧஔ:
CREATE GLOBAL TEMPORARY TABLE temp1
(

sessionid int)
on commit delete rows

. ЄӑBSQL
һӑBSQLZЕrھgr͈е,ھgr,Ñ˽ԃĽYҪԃČ,ͿʹÄӑBSQL.һ,ЄӑBSQLϵyYԴMͬӵoBSQLăɵ.ÿΈЄӑBSQLĕr򶼱MZ.춴c,ϣұMܵʹÄӑBSQL.
SQL Serverexec(ַ)ԈЄӑBSQL,ҪĈеĄӑBSQLеõYֵԒsp_executesql惦^.
Oracle,ʹDBMS_SQLexecute immediate ЄӑBSQL,^Ҫעexecute immediateOracle8iƳ,Oracle8ǰİ汾ǲ@Nʽ.
:
SQL SERVER:
declare @count int
declare @SQL nvarchar(200)
set @SQL = N'select count(*) from sysobjects'
exec sp_executesql @SQL,N'@i int output',@count output
print @count
Oracle:
set serveroutput on
/
declare 
i_count int ;
v_SQL varchar2(200) := 'begin select count(*) into :1 from user_objects; end;';
begin
execute immediate v_SQL using out i_count;
dbms_output.put_line(i_count);
end;
/

DBMS_SQLʹ^}s@ʽʹΘ,ᳫʹ,ʴ̎f.
]:һcҪע, execute immediate܈еַLƵ,^@,NֻDBMS_SQL.



. identityֲ?
SQL ServerеidentityԽo҂Ĺ˺ܴķ,҂M.OracleЅs]@ӵ.Oracleֻsequenceĸ.sequence՟oPԵ,sequenceKڱ,Ǫڵ,ǟoP.҂sequence_SQL SERVERidentityЧ.:
SQL SERVERԭY:
create table ftm07 -- Ʊ
( 
ftg00c TYPE_v_CMPID not null, -- ˾e 
ftg01c varchar(20) not null, -- Ʊ̖a 
ftg02f int identity not null, -- ˮ̖ 
ftg03d TYPE_v_DATE null , -- r̎ 
ftg04c TYPE_v_ENUM null , -- r 
ftg06c varchar(20) null , -- Ʊ̖ 
ftg07c varchar(2) null , -- Ʊӄe 
ftg08c varchar(20) null , -- Ʊӆ̖
constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
) 
ֲOracleе:
create table ftm07 -- Ʊ
( 
ftg00c varchar2(3) not null, -- ˾e 
ftg01c varchar2(20) not null, -- Ʊ̖a 
ftg02f int not null, -- ˮ̖ /* SEQUENCE */ 
ftg03d varchar2(8) null , -- r̎ 
ftg04c varchar2(1) null , -- r 
ftg06c varchar2(20) null , -- Ʊ̖ 
ftg07c varchar2(2) null , -- Ʊӄe 
ftg08c varchar2(20) null , -- Ʊӆ̖
constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
);
CREATE SEQUENCE ftm07_seq INCREMENT BY 1; 

create or replace TRIGGER Cash_ftm07_insert_before -- һinsert֮ǰ|l
before insert on ftm07
for each row
declare
i_id integer;
begin
select ftm07_seq.nextval into i_id from dual; 
:NEW.ftg02f := i_id;
end;
ԏ濴,ͬSQL SERVERһ,KҪftg02fֶMв,|l㶨һ.һͬҪע:sequenceֵֹܱ
SQL SERVERпͨ^dbcc checkident(,reseed,0)truncate table tableName(ҲҪԒ)еidentityλÞ1,Oraclesequence@c,sequenceֻ_ֵ,ϵyŕԄӌÞAСֵ.